package zjs.dc.controller;

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;

/**
 * @Class:
 * @Author: guoml
 * @Description: 针对企查查t_ipo_company_cash_flow_statement表和聚源LC_CashFlowStatementAll表进行数据映射开发
 * @Date: 2021/12/7 12:51
 */

public class TIpoCompanyCashFlowStatement {
    public static void main(String[] args) {
        SparkSession spark = SparkSession.builder().appName("t_ipo_company_cash_flow_statement").enableHiveSupport().getOrCreate();
        spark.sparkContext().setLogLevel("ERROR");
        //获取中数增量数据表中的新增数据（left join）
        Dataset<Row> insert_data = spark.sql("SELECT md5(concat(100,a.ID)) as ID,\n" +
                "       b.SecuCode,\n" +
                "to_json(array(named_struct('GoodsSaleServiceRenderCash',GoodsSaleServiceRenderCash,\n" +
                "'TaxLevyRefund',TaxLevyRefund,\n" +
                "'NetDepositIncrease',NetDepositIncrease,\n" +
                "'NetBorrowingFromCentralBank',NetBorrowingFromCentralBank,\n" +
                "'NetBorrowingFromFinanceCo',NetBorrowingFromFinanceCo,\n" +
                "'DrawBackLoansCanceled',DrawBackLoansCanceled,\n" +
                "'InterestAndCommissionCashIn',InterestAndCommissionCashIn,\n" +
                "'NetDealTradingAssets',NetDealTradingAssets,\n" +
                "'NetBuyBack',NetBuyBack,\n" +
                "'NetOriginalInsuranceCash',NetOriginalInsuranceCash,\n" +
                "'NetReinsuranceCash',NetReinsuranceCash,\n" +
                "'NetInsurerDepositInvestment',NetInsurerDepositInvestment,\n" +
                "'OtherCashInRelatedOperate',OtherCashInRelatedOperate,\n" +
                "'SpecialItemsOCIF',SpecialItemsOCIF,\n" +
                "'AdjustmentItemsOCIF',AdjustmentItemsOCIF,\n" +
                "'SubtotalOperateCashInflow',SubtotalOperateCashInflow,\n" +
                "'GoodsServicesCashPaid',GoodsServicesCashPaid,\n" +
                "'StaffBehalfPaid',StaffBehalfPaid,\n" +
                "'AllTaxesPaid',AllTaxesPaid,\n" +
                "'NetLoanAndAdvanceIncrease',NetLoanAndAdvanceIncrease,\n" +
                "'NetDepositInCBAndIB',NetDepositInCBAndIB,\n" +
                "'NetLendCapital',NetLendCapital,\n" +
                "'CommissionCashPaid',CommissionCashPaid,\n" +
                "'OriginalCompensationPaid',OriginalCompensationPaid,\n" +
                "'NetCashForReinsurance',NetCashForReinsurance,\n" +
                "'PolicyDividendCashPaid',PolicyDividendCashPaid,\n" +
                "'OtherOperateCashPaid',OtherOperateCashPaid,\n" +
                "'SpecialItemsOCOF',SpecialItemsOCOF,\n" +
                "'AdjustmentItemsOCOF',AdjustmentItemsOCOF,\n" +
                "'SubtotalOperateCashOutflow',SubtotalOperateCashOutflow,\n" +
                "'AdjustmentItemsNOCF',AdjustmentItemsNOCF,\n" +
                "'NetOperateCashFlow',NetOperateCashFlow,\n" +
                "'InvestWithdrawalCash',InvestWithdrawalCash,\n" +
                "'Investproceeds',Investproceeds,\n" +
                "'FixIntanOtherAssetDispoCash',FixIntanOtherAssetDispoCash,\n" +
                "'NetCashDealSubCompany',NetCashDealSubCompany,\n" +
                "'OtherCashFromInvestAct',OtherCashFromInvestAct,\n" +
                "'SpecialItemsICIF',SpecialItemsICIF,\n" +
                "'AdjustmentItemsICIF',AdjustmentItemsICIF,\n" +
                "'SubtotalInvestCashInflow',SubtotalInvestCashInflow,\n" +
                "'FixIntanOtherAssetAcquiCash',FixIntanOtherAssetAcquiCash,\n" +
                "'InvestCashPaid',InvestCashPaid,\n" +
                "'NetCashFromSubCompany',NetCashFromSubCompany,\n" +
                "'ImpawnedLoanNetIncrease',ImpawnedLoanNetIncrease,\n" +
                "'OtherCashToInvestAct',OtherCashToInvestAct,\n" +
                "'SpecialItemsICOF',SpecialItemsICOF,\n" +
                "'AdjustmentItemsICOF',AdjustmentItemsICOF,\n" +
                "'SubtotalInvestCashOutflow',SubtotalInvestCashOutflow,\n" +
                "'AdjustmentItemsNICF',AdjustmentItemsNICF,\n" +
                "'NetInvestCashFlow',NetInvestCashFlow,\n" +
                "'CashFromInvest',CashFromInvest,\n" +
                "'CashFromMinoSInvestSub',CashFromMinoSInvestSub,\n" +
                "'CashFromBondsIssue',CashFromBondsIssue,\n" +
                "'CashFromBorrowing',CashFromBorrowing,\n" +
                "'OtherFinanceActCash',OtherFinanceActCash,\n" +
                "'SpecialItemsFCIF',SpecialItemsFCIF,\n" +
                "'AdjustmentItemsFCIF',AdjustmentItemsFCIF,\n" +
                "'SubtotalFinanceCashInflow',SubtotalFinanceCashInflow,\n" +
                "'BorrowingRepayment',BorrowingRepayment,\n" +
                "'DividendInterestPayment',DividendInterestPayment,\n" +
                "'ProceedsFromSubToMinoS',ProceedsFromSubToMinoS,\n" +
                "'OtherFinanceActPayment',OtherFinanceActPayment,\n" +
                "'SpecialItemsFCOF',SpecialItemsFCOF,\n" +
                "'AdjustmentItemsFCOF',AdjustmentItemsFCOF,\n" +
                "'SubtotalFinanceCashOutflow',SubtotalFinanceCashOutflow,\n" +
                "'AdjustmentItemsNFCF',AdjustmentItemsNFCF,\n" +
                "'NetFinanceCashFlow',NetFinanceCashFlow,\n" +
                "'ExchanRateChangeEffect',ExchanRateChangeEffect,\n" +
                "'OtherItemsEffectingCE',OtherItemsEffectingCE,\n" +
                "'AdjustmentItemsCE',AdjustmentItemsCE,\n" +
                "'CashEquivalentIncrease',CashEquivalentIncrease,\n" +
                "'BeginPeriodCash',BeginPeriodCash,\n" +
                "'OtherItemsEffectingCEI',OtherItemsEffectingCEI,\n" +
                "'AdjustmentItemsCEI',AdjustmentItemsCEI,\n" +
                "'EndPeriodCashEquivalent',EndPeriodCashEquivalent,\n" +
                "'NetProfit',NetProfit,\n" +
                "'NPParentCompanyOwners',NPParentCompanyOwners,\n" +
                "'MinorityProfit',MinorityProfit,\n" +
                "'AssetsDepreciationReserves',AssetsDepreciationReserves,\n" +
                "'FixedAssetDepreciation',FixedAssetDepreciation,\n" +
                "'ProductBioAssetsDep',ProductBioAssetsDep,\n" +
                "'IntangibleAssetAmortization',IntangibleAssetAmortization,\n" +
                "'DeferredExpenseAmort',DeferredExpenseAmort,\n" +
                "'DeferredExpenseDecreased',DeferredExpenseDecreased,\n" +
                "'AccruedExpenseAdded',AccruedExpenseAdded,\n" +
                "'FixIntanOtherAssetDispoLoss',FixIntanOtherAssetDispoLoss,\n" +
                "'FixedAssetScrapLoss',FixedAssetScrapLoss,\n" +
                "'LossFromFairValueChanges',LossFromFairValueChanges,\n" +
                "'FinancialExpense',FinancialExpense,\n" +
                "'InvestLoss',InvestLoss,\n" +
                "'DeferedTaxAssetDecrease',DeferedTaxAssetDecrease,\n" +
                "'DeferedTaxLiabilityIncrease',DeferedTaxLiabilityIncrease,\n" +
                "'InventoryDecrease',InventoryDecrease,\n" +
                "'OperateReceivableDecrease',OperateReceivableDecrease,\n" +
                "'OperatePayableIncrease',OperatePayableIncrease,\n" +
                "'Others',Others,\n" +
                "'SpecialItemsNOCF1',SpecialItemsNOCF1,\n" +
                "'AdjustmentItemsNOCF1',AdjustmentItemsNOCF1,\n" +
                "'NetOperateCashFlowNotes',NetOperateCashFlowNotes,\n" +
                "'ContrastAdjutmentNOCF',ContrastAdjutmentNOCF,\n" +
                "'DebtToCaptical',DebtToCaptical,\n" +
                "'CBsExpiringWithin1Y',CBsExpiringWithin1Y,\n" +
                "'FixedAssetsFinanceLeases',FixedAssetsFinanceLeases,\n" +
                "'CashAtEndOfYear',CashAtEndOfYear,\n" +
                "'CashAtBeginningOfYear',CashAtBeginningOfYear,\n" +
                "'CashEquivalentsAtEndOfYear',CashEquivalentsAtEndOfYear,\n" +
                "'CashEquivalentsAtBeginning',CashEquivalentsAtBeginning,\n" +
                "'SpecialItemsC',SpecialItemsC,\n" +
                "'AdjustmentItemsC',AdjustmentItemsC,\n" +
                "'NetIncrInCashAndEquivalents',NetIncrInCashAndEquivalents,\n" +
                "'ContrastAdjutmentNC',ContrastAdjutmentNC,\n" +
                "'InvestPropertyDA',InvestPropertyDA,\n" +
                "'NetIncBorFunds',NetIncBorFunds,\n" +
                "'NetCashRecInVTS',NetCashRecInVTS,\n" +
                "'NetCashRecAgeUTS',NetCashRecAgeUTS,\n" +
                "'NetIncFinAssTraPurp',NetIncFinAssTraPurp,\n" +
                "'NetIncCapResBusOper',NetIncCapResBusOper,\n" +
                "'NetIncCapResBusInv',NetIncCapResBusInv,\n" +
                "'CashRecIssOthEquIns',CashRecIssOthEquIns,\n" +
                "'NetBuyBackFin',NetBuyBackFin,\n" +
                "'IncResFunding',IncResFunding,\n" +
                "'IncSpeReserves',IncSpeReserves,\n" +
                "'CreditImpairmentL',CreditImpairmentL,\n" +
                "'DefProceedsAmo',DefProceedsAmo,\n" +
                "'IncEstLiability',IncEstLiability,\n" +
                "'NetDecFinancialAsset',NetDecFinancialAsset,\n" +
                "'NetCashPaidInVTS',NetCashPaidInVTS,\n" +
                "'UsufructAssetsDA',UsufructAssetsDA,\n" +
                "'NetDecLoanAndAdvance',NetDecLoanAndAdvance,\n" +
                "'NetDecreaseInCBAndIB',NetDecreaseInCBAndIB,\n" +
                "'NetDecFundLending',NetDecFundLending,\n" +
                "'NetDecCapResBusOper',NetDecCapResBusOper,\n" +
                "'NetDecFinAssTraPurp',NetDecFinAssTraPurp,\n" +
                "'NetIncFinLiaTraPurp',NetIncFinLiaTraPurp,\n" +
                "'BFLAFValOnPLChange',BFLAFValOnPLChange,\n" +
                "'NetDecBorrowFromCB',NetDecBorrowFromCB,\n" +
                "'NetDecBorFromFinanceCo',NetDecBorFromFinanceCo,\n" +
                "'NetDecInsurDPSTInvest',NetDecInsurDPSTInvest,\n" +
                "'NetDecBorrowingCapital',NetDecBorrowingCapital,\n" +
                "'NetDecOfBuyBack',NetDecOfBuyBack,\n" +
                "'NetCashPayAgeUTS',NetCashPayAgeUTS,\n" +
                "'NetDecDealTradeAssets',NetDecDealTradeAssets,\n" +
                "'NetDecFinLiaTraPurp',NetDecFinLiaTraPurp,\n" +
                "'OpeAndAdmExpForCash',OpeAndAdmExpForCash,\n" +
                "'NetIncFinancialAsset',NetIncFinancialAsset,\n" +
                "'NetDecBuyBackFin',NetDecBuyBackFin,\n" +
                "'InterestIncome',InterestIncome,\n" +
                "'InterestExpense',InterestExpense,\n" +
                "'LeaseLiaIntExp',LeaseLiaIntExp,\n" +
                "'BondIssueExpense',BondIssueExpense,\n" +
                "'ExchangeLoss',ExchangeLoss,\n" +
                "'DeferredTaxCredit',DeferredTaxCredit,\n" +
                "'SharePayment',SharePayment,\n" +
                "'DecreaseTradeAssets',DecreaseTradeAssets,\n" +
                "'DecAvailableSaleAssets',DecAvailableSaleAssets,\n" +
                "'DecreaseLoan',DecreaseLoan))) as report_json,\n" +
                "       '' as created_date,\n" +
                "       '' as update_date,\n" +
                "       a.EndDate,\n" +
                "       '' as report_encrypt_key,\n" +
                "       regexp_replace(current_date(),'-',''),\n" +
                "       '1' as isadd\n" +
                "FROM\n" +
                "  (select a.* from (select a.*,row_number ( ) over ( PARTITION BY CompanyCode,EndDate ORDER BY InfoPublDate desc,IfAdjusted) AS adjust from a_dc_ep_ods.LC_CashFlowStatementAll a where IfMerged = 1 and IfComplete = 1 and IfAdjusted in (1,2) and InfoSourceCode in (110101,110102,110103,110104))a where a.adjust=1)a\n" +
                "INNER JOIN\n" +
                "  (select * from a_dc_ep_ods.SecuMain where ListedState = 1 and ListedSector not in (3,7) and SecuCategory = 1) b on a.CompanyCode = b.CompanyCode").union(spark.sql("SELECT md5(concat(200,a.ID)) as ID,\n" +
                "       b.SecuCode,\n" +
                "to_json(array(named_struct('GSServRECVCSH',GSServRECVCSH,\n" +
                "'TaxLevyRefund',TaxLevyRefund,\n" +
                "'NetDPSTIncrease',NetDPSTIncrease,\n" +
                "'NetBorrowFromCB',NetBorrowFromCB,\n" +
                "'NetLendFinanceC',NetLendFinanceC,\n" +
                "'INTCommCashIn',INTCommCashIn,\n" +
                "'NetDealTradeAss',NetDealTradeAss,\n" +
                "'NetBuyBack',NetBuyBack,\n" +
                "'NOrigInsurCash',NOrigInsurCash,\n" +
                "'NetReinsurCash',NetReinsurCash,\n" +
                "'NInsurDPSTInves',NInsurDPSTInves,\n" +
                "'OtherRelateOCIn',OtherRelateOCIn,\n" +
                "'SpecialItemOCIF',SpecialItemOCIF,\n" +
                "'AdjustItemsOCIF',AdjustItemsOCIF,\n" +
                "'SubTOCInflow',SubTOCInflow,\n" +
                "'GoodsServCashPa',GoodsServCashPa,\n" +
                "'StaffBehalfPaid',StaffBehalfPaid,\n" +
                "'AllTaxesPaid',AllTaxesPaid,\n" +
                "'NLoanAndAdvIncr',NLoanAndAdvIncr,\n" +
                "'NDPSTInCBAndIB',NDPSTInCBAndIB,\n" +
                "'IntAndCommCSHPa',IntAndCommCSHPa,\n" +
                "'OrigCompensPaid',OrigCompensPaid,\n" +
                "'PolicyDivCashPa',PolicyDivCashPa,\n" +
                "'OtherOCPaid',OtherOCPaid,\n" +
                "'SpecialItemOCOF',SpecialItemOCOF,\n" +
                "'AdjustItemsOCOF',AdjustItemsOCOF,\n" +
                "'SubTOCOutflow',SubTOCOutflow,\n" +
                "'AdjustItemsNOCF',AdjustItemsNOCF,\n" +
                "'NetOCFlow',NetOCFlow,\n" +
                "'InvestWDRLCash',InvestWDRLCash,\n" +
                "'BuyBackSaleWDRLCash',BuyBackSaleWDRLCash,\n" +
                "'OtherInvestWDRLCash',OtherInvestWDRLCash,\n" +
                "'Investproceeds',Investproceeds,\n" +
                "'FixIntaOADSPCSH',FixIntaOADSPCSH,\n" +
                "'NCashDealSubCom',NCashDealSubCom,\n" +
                "'OtherIC',OtherIC,\n" +
                "'SpecialItemICIF',SpecialItemICIF,\n" +
                "'AdjustItemsICIF',AdjustItemsICIF,\n" +
                "'SubTICInflow',SubTICInflow,\n" +
                "'FixIntaOAAcqCSH',FixIntaOAAcqCSH,\n" +
                "'InvestCashPaid',InvestCashPaid,\n" +
                "'BuyBackSaleCashPaid',BuyBackSaleCashPaid,\n" +
                "'OtherInvestCashPaid',OtherInvestCashPaid,\n" +
                "'NetCashFrSubCom',NetCashFrSubCom,\n" +
                "'ImpawnLoanNIncr',ImpawnLoanNIncr,\n" +
                "'OthCSHInvestAct',OthCSHInvestAct,\n" +
                "'SpecialItemICOF',SpecialItemICOF,\n" +
                "'AdjustItemsICOF',AdjustItemsICOF,\n" +
                "'SubTICOF',SubTICOF,\n" +
                "'AdjustItemsNICF',AdjustItemsNICF,\n" +
                "'NetICFlow',NetICFlow,\n" +
                "'CashFromInvest',CashFromInvest,\n" +
                "'SubMinInvestCSH',SubMinInvestCSH,\n" +
                "'CashFrBondsIss',CashFrBondsIss,\n" +
                "'CashFrBorrowing',CashFrBorrowing,\n" +
                "'OtherFCReceive',OtherFCReceive,\n" +
                "'SpecialItemFCIF',SpecialItemFCIF,\n" +
                "'AdjustItemsFCIF',AdjustItemsFCIF,\n" +
                "'SubtotalFCIF',SubtotalFCIF,\n" +
                "'BorrowingRepay',BorrowingRepay,\n" +
                "'DividendIntPay',DividendIntPay,\n" +
                "'ProceedSubToMin',ProceedSubToMin,\n" +
                "'OtherFCPayment',OtherFCPayment,\n" +
                "'SpecialItemFCOF',SpecialItemFCOF,\n" +
                "'AdjustItemsFCOF',AdjustItemsFCOF,\n" +
                "'SubTFCOutflow',SubTFCOutflow,\n" +
                "'AdjustItemsNFCF',AdjustItemsNFCF,\n" +
                "'NetFCFlow',NetFCFlow,\n" +
                "'ExchaRChangeEff',ExchaRChangeEff,\n" +
                "'OthItemEffectCE',OthItemEffectCE,\n" +
                "'AdjustItemsCE',AdjustItemsCE,\n" +
                "'CEIncrease',CEIncrease,\n" +
                "'BeginPeriodCash',BeginPeriodCash,\n" +
                "'EndPeriodCE',EndPeriodCE,\n" +
                "'NetProfit',NetProfit,\n" +
                "'MinorityProfit',MinorityProfit,\n" +
                "'AssetsDeprReser',AssetsDeprReser,\n" +
                "'IAAmortization',IAAmortization,\n" +
                "'DeferExpsAmort',DeferExpsAmort,\n" +
                "'DeferExpsDecre',DeferExpsDecre,\n" +
                "'AccrdExpsAdded',AccrdExpsAdded,\n" +
                "'FixIntaOADSPLos',FixIntaOADSPLos,\n" +
                "'FAScrapLoss',FAScrapLoss,\n" +
                "'LossFrFVChanges',LossFrFVChanges,\n" +
                "'FinancialExps',FinancialExps,\n" +
                "'InvestLoss',InvestLoss,\n" +
                "'DeferTXAssetDec',DeferTXAssetDec,\n" +
                "'DeferTaxLiIncre',DeferTaxLiIncre,\n" +
                "'InventoryDecre',InventoryDecre,\n" +
                "'OperaReceivaDec',OperaReceivaDec,\n" +
                "'OperaPayableInc',OperaPayableInc,\n" +
                "'Others',Others,\n" +
                "'SpecITMNOCFNote',SpecITMNOCFNote,\n" +
                "'AdjItemNOCFNote',AdjItemNOCFNote,\n" +
                "'NetOCFNotes',NetOCFNotes,\n" +
                "'ContrastAdjNOCF',ContrastAdjNOCF,\n" +
                "'DebtToCaptical',DebtToCaptical,\n" +
                "'CBsExpiringInOY',CBsExpiringInOY,\n" +
                "'FAFinanceLeases',FAFinanceLeases,\n" +
                "'CashAtEndOfYear',CashAtEndOfYear,\n" +
                "'CashAtBOY',CashAtBOY,\n" +
                "'CEAtEOY',CEAtEOY,\n" +
                "'CEAtBOY',CEAtBOY,\n" +
                "'SpecialItemsC',SpecialItemsC,\n" +
                "'AdjItemsCash',AdjItemsCash,\n" +
                "'NetIncrInCE',NetIncrInCE,\n" +
                "'ContrastAdjNC',ContrastAdjNC,\n" +
                "'EndPeriodCENote',EndPeriodCENote,\n" +
                "'DrawBackLNCanc',DrawBackLNCanc,\n" +
                "'NetLendCapital',NetLendCapital,\n" +
                "'NCForReinsur',NCForReinsur,\n" +
                "'AdjItemsCEI',AdjItemsCEI,\n" +
                "'OtherItemEffCEI',OtherItemEffCEI,\n" +
                "'NetDealTradeLiability',NetDealTradeLiability,\n" +
                "'NetDecFinancialAsset',NetDecFinancialAsset,\n" +
                "'NetCashReceiveInVTS',NetCashReceiveInVTS,\n" +
                "'NetDecreaseInCBAndIB',NetDecreaseInCBAndIB,\n" +
                "'NetIncFinLibAtFValTPL',NetIncFinLibAtFValTPL,\n" +
                "'NetDecFinAetAtFValTPL',NetDecFinAetAtFValTPL,\n" +
                "'NetDecFundLending',NetDecFundLending,\n" +
                "'NetDecSellbackAssets',NetDecSellbackAssets,\n" +
                "'NetDecLoanAndAdvance',NetDecLoanAndAdvance,\n" +
                "'OpeAndAdmExpForCash',OpeAndAdmExpForCash,\n" +
                "'NetCashFinAetAtFValTPL',NetCashFinAetAtFValTPL,\n" +
                "'NetCashFinLibAtFValTPL',NetCashFinLibAtFValTPL,\n" +
                "'NetDecInsurDPSTInvest',NetDecInsurDPSTInvest,\n" +
                "'NetIncFinancialAsset',NetIncFinancialAsset,\n" +
                "'NetCashPaidInVTS',NetCashPaidInVTS,\n" +
                "'BFLAFValOnPLChange',BFLAFValOnPLChange,\n" +
                "'NetDecBorrowFromCB',NetDecBorrowFromCB,\n" +
                "'NetDecFinLibAtFValTPL',NetDecFinLibAtFValTPL,\n" +
                "'NetIncFinAetAtFValTPL',NetIncFinAetAtFValTPL,\n" +
                "'NetDecBorrowingCapital',NetDecBorrowingCapital,\n" +
                "'NetDecRepurchaseBC',NetDecRepurchaseBC,\n" +
                "'NetIncSellbackAssets',NetIncSellbackAssets,\n" +
                "'InterestExpenses',InterestExpenses,\n" +
                "'DecreaseTradeAssets',DecreaseTradeAssets,\n" +
                "'DecAvailableSaleAssets',DecAvailableSaleAssets,\n" +
                "'DecreaseLoan',DecreaseLoan,\n" +
                "'IncreaseDeposits',IncreaseDeposits,\n" +
                "'DecOtherOperaAsset',DecOtherOperaAsset,\n" +
                "'IncOtherOperaLiability',IncOtherOperaLiability,\n" +
                "'GuarantCash',GuarantCash,\n" +
                "'ReGuarantCash',ReGuarantCash,\n" +
                "'SecuredPaymentCash',SecuredPaymentCash,\n" +
                "'NetDealTradeAsset',NetDealTradeAsset,\n" +
                "'GuarantIndemCashPa',GuarantIndemCashPa,\n" +
                "'GuarantReimnurCashPa',GuarantReimnurCashPa,\n" +
                "'SellRepoInvestReceive',SellRepoInvestReceive,\n" +
                "'SellRepoInvestPayment',SellRepoInvestPayment,\n" +
                "'NetIncBorFunds',NetIncBorFunds,\n" +
                "'GuarantCompensateRSRV',GuarantCompensateRSRV,\n" +
                "'NetDeferedTaxCredit',NetDeferedTaxCredit,\n" +
                "'DefProceedsAmo',DefProceedsAmo,\n" +
                "'IncDefIncome',IncDefIncome,\n" +
                "'BondIssueExpense',BondIssueExpense,\n" +
                "'SharePayment',SharePayment,\n" +
                "'EquityIncentExpense',EquityIncentExpense,\n" +
                "'ExchangeLoss',ExchangeLoss,\n" +
                "'NetDecOfBuyBack',NetDecOfBuyBack,\n" +
                "'DecFinAndEquityAssets',DecFinAndEquityAssets,\n" +
                "'InterestIncome',InterestIncome,\n" +
                "'DecOtherCurrentAssets',DecOtherCurrentAssets,\n" +
                "'OtherRevenue',OtherRevenue,\n" +
                "'DecProductBioAssets',DecProductBioAssets,\n" +
                "'DividendReceive',DividendReceive,\n" +
                "'InvestManageIncome',InvestManageIncome,\n" +
                "'DecRestrictMoney',DecRestrictMoney,\n" +
                "'RightUseAssetsDep',RightUseAssetsDep,\n" +
                "'WithdrawGuaranteeReser',WithdrawGuaranteeReser,\n" +
                "'ExtractFutureRisk',ExtractFutureRisk,\n" +
                "'InvestPropertyDA',InvestPropertyDA,\n" +
                "'CreditImpairmentL',CreditImpairmentL,\n" +
                "'IncEstLiability',IncEstLiability,\n" +
                "'FAssetDep',FAssetDep,\n" +
                "'FixedAssetDepr',FixedAssetDepr,\n" +
                "'ProductBioAssetsDep',ProductBioAssetsDep,\n" +
                "'ImpairLoanIncome',ImpairLoanIncome,\n" +
                "'ImpairFinAssetsIncome',ImpairFinAssetsIncome,\n" +
                "'AssetImpairmentLoss',AssetImpairmentLoss,\n" +
                "'InvestManagePay',InvestManagePay,\n" +
                "'IncSpeReserves',IncSpeReserves))) as report_json,\n" +
                "       '' as created_date,\n" +
                "       '' as update_date,\n" +
                "       a.EndDate,\n" +
                "       '' as report_encrypt_key,\n" +
                "       regexp_replace(current_date(),'-',''),\n" +
                "       '1' as isadd\n" +
                "FROM\n" +
                "  (select a.* from (select a.*,row_number ( ) over ( PARTITION BY CompanyCode,EndDate ORDER BY InfoPublDate desc,IfAdjusted) AS adjust from a_dc_ep_ods.NQ_CFStatement a where IfMerged = 1 and IfComplete = 1 and IfAdjusted in (1,2) and InfoSource in (2,3,4,5))a where a.adjust = 1)a\n" +
                "INNER JOIN\n" +
                "  (select * from a_dc_ep_ods.SecuMain where ListedState = 1 and ListedSector =3 and SecuCategory = 1) b on a.CompanyCode = b.CompanyCode")).union(spark.sql("SELECT md5(concat(300,a.ID)) as ID,\n" +
                "       b.SecuCode,\n" +
                "to_json(array(named_struct('FinRepFormat',FinRepFormat,\n" +
                "'GdSaSeReCash',GdSaSeReCash,\n" +
                "'TaxLevyRefund',TaxLevyRefund,\n" +
                "'NetDepositIncrease',NetDepositIncrease,\n" +
                "'NetBorFromCB',NetBorFromCB,\n" +
                "'NetBorFromFinCo',NetBorFromFinCo,\n" +
                "'DrawBackLoansCanceled',DrawBackLoansCanceled,\n" +
                "'IntAndComCashIn',IntAndComCashIn,\n" +
                "'NetDealTradingAssets',NetDealTradingAssets,\n" +
                "'NetBuyBack',NetBuyBack,\n" +
                "'NetOriInsCash',NetOriInsCash,\n" +
                "'NetReinsuranceCash',NetReinsuranceCash,\n" +
                "'NetInsDepInv',NetInsDepInv,\n" +
                "'OthCashInRelOpe',OthCashInRelOpe,\n" +
                "'SpecialItemsOCIF',SpecialItemsOCIF,\n" +
                "'AdjustmentItemsOCIF',AdjustmentItemsOCIF,\n" +
                "'SuOpCashInflow',SuOpCashInflow,\n" +
                "'GoodsServicesCashPaid',GoodsServicesCashPaid,\n" +
                "'StaffBehalfPaid',StaffBehalfPaid,\n" +
                "'AllTaxesPaid',AllTaxesPaid,\n" +
                "'NetLAdvInc',NetLAdvInc,\n" +
                "'NetDepositInCBAndIB',NetDepositInCBAndIB,\n" +
                "'NetLendCapital',NetLendCapital,\n" +
                "'ComCashPaid',ComCashPaid,\n" +
                "'OriCompPaid',OriCompPaid,\n" +
                "'NetCashForReinsurance',NetCashForReinsurance,\n" +
                "'PolicyDividendCashPaid',PolicyDividendCashPaid,\n" +
                "'OtherOperateCashPaid',OtherOperateCashPaid,\n" +
                "'SpecialItemsOCOF',SpecialItemsOCOF,\n" +
                "'AdjustmentItemsOCOF',AdjustmentItemsOCOF,\n" +
                "'SubOpCOutflow',SubOpCOutflow,\n" +
                "'AdjustmentItemsNOCF',AdjustmentItemsNOCF,\n" +
                "'NetOperateCashFlow',NetOperateCashFlow,\n" +
                "'InvestWithdrawalCash',InvestWithdrawalCash,\n" +
                "'Investproceeds',Investproceeds,\n" +
                "'FixInOtADisCash',FixInOtADisCash,\n" +
                "'NetCashDealSubCompany',NetCashDealSubCompany,\n" +
                "'OtherCashFromInvestAct',OtherCashFromInvestAct,\n" +
                "'SpecialItemsICIF',SpecialItemsICIF,\n" +
                "'AdjustmentItemsICIF',AdjustmentItemsICIF,\n" +
                "'SubInvCaInflow',SubInvCaInflow,\n" +
                "'FixInOAsAcCash',FixInOAsAcCash,\n" +
                "'InvestCashPaid',InvestCashPaid,\n" +
                "'NetCashFromSubCompany',NetCashFromSubCompany,\n" +
                "'ImLoanNetInc',ImLoanNetInc,\n" +
                "'OtherCashToInvestAct',OtherCashToInvestAct,\n" +
                "'SpecialItemsICOF',SpecialItemsICOF,\n" +
                "'AdjustmentItemsICOF',AdjustmentItemsICOF,\n" +
                "'SubInvCashOflow',SubInvCashOflow,\n" +
                "'AdjustmentItemsNICF',AdjustmentItemsNICF,\n" +
                "'NetInvestCashFlow',NetInvestCashFlow,\n" +
                "'CashFromInvest',CashFromInvest,\n" +
                "'CashFromMinoSInvestSub',CashFromMinoSInvestSub,\n" +
                "'CashFromBondsIssue',CashFromBondsIssue,\n" +
                "'CashFromBorrowing',CashFromBorrowing,\n" +
                "'OtherFinanceActCash',OtherFinanceActCash,\n" +
                "'SpecialItemsFCIF',SpecialItemsFCIF,\n" +
                "'AdjustmentItemsFCIF',AdjustmentItemsFCIF,\n" +
                "'SubFinCashInfl',SubFinCashInfl,\n" +
                "'BorrowingRepayment',BorrowingRepayment,\n" +
                "'DivIntPayment',DivIntPayment,\n" +
                "'ProceedsFromSubToMinoS',ProceedsFromSubToMinoS,\n" +
                "'OtherFinanceActPayment',OtherFinanceActPayment,\n" +
                "'SpecialItemsFCOF',SpecialItemsFCOF,\n" +
                "'AdjustmentItemsFCOF',AdjustmentItemsFCOF,\n" +
                "'SubFinCOflow',SubFinCOflow,\n" +
                "'AdjustmentItemsNFCF',AdjustmentItemsNFCF,\n" +
                "'NetFinanceCashFlow',NetFinanceCashFlow,\n" +
                "'ExchanRateChangeEffect',ExchanRateChangeEffect,\n" +
                "'OtherItemsEffectingCE',OtherItemsEffectingCE,\n" +
                "'AdjustmentItemsCE',AdjustmentItemsCE,\n" +
                "'CashEquivalentIncrease',CashEquivalentIncrease,\n" +
                "'BeginPeriodCash',BeginPeriodCash,\n" +
                "'OtherItemsEffectingCEI',OtherItemsEffectingCEI,\n" +
                "'AdjustmentItemsCEI',AdjustmentItemsCEI,\n" +
                "'EndPerCEqu',EndPerCEqu,\n" +
                "'NetProfit',NetProfit,\n" +
                "'NPParentCompanyOwners',NPParentCompanyOwners,\n" +
                "'MinorityProfit',MinorityProfit,\n" +
                "'ADepreReserves',ADepreReserves,\n" +
                "'FixedAssetDepreciation',FixedAssetDepreciation,\n" +
                "'ProductBioAssetsDep',ProductBioAssetsDep,\n" +
                "'InvestPropertyDA',InvestPropertyDA,\n" +
                "'IntAAmort',IntAAmort,\n" +
                "'DeferredExpenseAmort',DeferredExpenseAmort,\n" +
                "'DefExpDecd',DefExpDecd,\n" +
                "'AccruedExpenseAdded',AccruedExpenseAdded,\n" +
                "'FixInOthADLoss',FixInOthADLoss,\n" +
                "'FixedAssetScrapLoss',FixedAssetScrapLoss,\n" +
                "'LFromFValueChg',LFromFValueChg,\n" +
                "'FinancialExpense',FinancialExpense,\n" +
                "'InvestLoss',InvestLoss,\n" +
                "'LeaseLiaIntExp',LeaseLiaIntExp,\n" +
                "'BondIssueExpense',BondIssueExpense,\n" +
                "'DefTaxAssetDec',DefTaxAssetDec,\n" +
                "'DefTaxLiaInc',DefTaxLiaInc,\n" +
                "'InventoryDecrease',InventoryDecrease,\n" +
                "'OpeRecDec',OpeRecDec,\n" +
                "'OperatePayableIncrease',OperatePayableIncrease,\n" +
                "'Others',Others,\n" +
                "'SpecITMNOCFNote',SpecITMNOCFNote,\n" +
                "'AdjItemNOCFNote',AdjItemNOCFNote,\n" +
                "'NetOpeCFNotes',NetOpeCFNotes,\n" +
                "'ContrastAdjutmentNOCF',ContrastAdjutmentNOCF,\n" +
                "'DebtToCaptical',DebtToCaptical,\n" +
                "'CBsExpiringInOY',CBsExpiringInOY,\n" +
                "'FixedAFinLeases',FixedAFinLeases,\n" +
                "'CashAtEndOfYear',CashAtEndOfYear,\n" +
                "'CashAtBeginningOfYear',CashAtBeginningOfYear,\n" +
                "'CEquAtEOfYear',CEquAtEOfYear,\n" +
                "'CEquAtBeginning',CEquAtBeginning,\n" +
                "'SpecialItemsC',SpecialItemsC,\n" +
                "'AdjustmentItemsC',AdjustmentItemsC,\n" +
                "'NetIncrInCEqu',NetIncrInCEqu,\n" +
                "'ContrastAdjutmentNC',ContrastAdjutmentNC,\n" +
                "'NetIncBorFunds',NetIncBorFunds,\n" +
                "'NetCashRecInVTS',NetCashRecInVTS,\n" +
                "'NetCashRecAgeUTS',NetCashRecAgeUTS,\n" +
                "'NetIncFinAssTraPurp',NetIncFinAssTraPurp,\n" +
                "'NetIncCapResBusOper',NetIncCapResBusOper,\n" +
                "'NetIncCapResBusInv',NetIncCapResBusInv,\n" +
                "'CashRecIssOthEquIns',CashRecIssOthEquIns,\n" +
                "'NetBuyBackFin',NetBuyBackFin,\n" +
                "'InterestExpense',InterestExpense,\n" +
                "'IncResFunding',IncResFunding,\n" +
                "'IncSpeReserves',IncSpeReserves,\n" +
                "'CreditImpairmentL',CreditImpairmentL,\n" +
                "'DefProceedsAmo',DefProceedsAmo,\n" +
                "'IncEstLiability',IncEstLiability,\n" +
                "'NetDecFinancialAsset',NetDecFinancialAsset,\n" +
                "'NetCashPaidInVTS',NetCashPaidInVTS,\n" +
                "'UsufructAssetsDA',UsufructAssetsDA,\n" +
                "'NetDecLoanAndAdvance',NetDecLoanAndAdvance,\n" +
                "'NetDecreaseInCBAndIB',NetDecreaseInCBAndIB,\n" +
                "'NetDecFundLending',NetDecFundLending,\n" +
                "'NetDecCapResBusOper',NetDecCapResBusOper,\n" +
                "'NetDecFinAssTraPurp',NetDecFinAssTraPurp,\n" +
                "'NetIncFinLiaTraPurp',NetIncFinLiaTraPurp,\n" +
                "'BFLAFValOnPLChange',BFLAFValOnPLChange,\n" +
                "'NetDecBorrowFromCB',NetDecBorrowFromCB,\n" +
                "'NetDecBorFromFinanceCo',NetDecBorFromFinanceCo,\n" +
                "'NetDecInsurDPSTInvest',NetDecInsurDPSTInvest,\n" +
                "'NetDecBorrowingCapital',NetDecBorrowingCapital,\n" +
                "'NetDecOfBuyBack',NetDecOfBuyBack,\n" +
                "'NetCashPayAgeUTS',NetCashPayAgeUTS,\n" +
                "'NetDecDealTradeAssets',NetDecDealTradeAssets,\n" +
                "'NetDecFinLiaTraPurp',NetDecFinLiaTraPurp,\n" +
                "'OpeAndAdmExpForCash',OpeAndAdmExpForCash,\n" +
                "'NetIncFinancialAsset',NetIncFinancialAsset,\n" +
                "'NetDecBuyBackFin',NetDecBuyBackFin,\n" +
                "'InterestIncome',InterestIncome,\n" +
                "'ExchangeLoss',ExchangeLoss,\n" +
                "'DeferredTaxCredit',DeferredTaxCredit,\n" +
                "'SharePayment',SharePayment,\n" +
                "'DecreaseTradeAssets',DecreaseTradeAssets,\n" +
                "'DecAvailableSaleAssets',DecAvailableSaleAssets,\n" +
                "'DecreaseLoan',DecreaseLoan))) as report_json,\n" +
                "       '' as created_date,\n" +
                "       '' as update_date,\n" +
                "       a.EndDate,\n" +
                "       '' as report_encrypt_key,\n" +
                "       regexp_replace(current_date(),'-',''),\n" +
                "       '1' as isadd\n" +
                "FROM\n" +
                "  (select a.* from (select a.*,row_number ( ) over ( PARTITION BY CompanyCode,EndDate ORDER BY InfoPublDate desc,IfAdjusted) AS adjust from a_dc_ep_ods.LC_STIBCashFlowState a where IfMerged = 1 and IfComplete = 1 and IfAdjusted in (1,2) and InfoSourceCode in (110101,110102,110103,110104))a where a.adjust = 1)a\n" +
                "INNER JOIN\n" +
                "  (select * from a_dc_ep_ods.SecuMain where ListedState = 1 and ListedSector =7 and SecuCategory = 1) b on a.CompanyCode = b.CompanyCode"));
        insert_data.createOrReplaceTempView("tmp_t_ipo_company_cash_flow_statement1");
        spark.sql("insert overwrite table a_dc_ep_incr.t_ipo_company_cash_flow_statement select * from tmp_t_ipo_company_cash_flow_statement1");
        spark.stop();
    }
}
